/*
 * TMap Designer - A Thematic Map design software
 * Copyright (C) 2006 World Health Organization
 * http://www.who.int
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 */
package who.tmap.imp.impl;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import who.tmap.bo.AttrDef;
import who.tmap.bo.Attrs;
import who.tmap.bo.impl.BizObjectFactory;
import who.tmap.common.TMapException;
import who.tmap.imp.ExcelImportAdapter;
import who.tmap.imp.ImportAdapterException;

/**
 * @author stl
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class ExcelImportAdapterImpl extends    BaseImportAdapterImpl 
									implements ExcelImportAdapter {  
    
    private static Logger logger = Logger.getLogger(ExcelImportAdapterImpl.class);	
	
    protected String fileName;
    protected String workSheetName;
    protected boolean firstRowContainsFieldNames;    
    
    protected POIFSFileSystem fs;
    protected HSSFWorkbook wb;
    protected HSSFSheet ws;    
      
    public ExcelImportAdapterImpl() {
        setName( "MS Excel" );
    }

    public void configure(Map<String, String> config) throws TMapException {
    	this.config =config;
    	config.put( DS_TYPE, ExcelImportAdapter.class.getName() );
    	
        fileName =(String)config.get( FILE_NAME );
        workSheetName =(String)config.get( WORK_SHEET );
        firstRowContainsFieldNames =Boolean.parseBoolean( config.get( FIRST_ROW_CONTAINS_FIELD_NAMES ) );       
    }
    
    public void connect() throws TMapException {
        try {
            fs =new POIFSFileSystem(new FileInputStream(fileName));
        } catch( Exception e ) {
            throw new ImportAdapterException( e );
        }
    }
    
    public void disconnect() {
        // nothing to do for excel
    }
      
	/**
 	 * Retrieve the list of tables from the data source schema.
 	 * A sheet stands for a table in Excel case
	 * @param schema
	 * @return
	 * @throws TMapException
	 */
    public Set<String> getWorkSheets() throws TMapException {
        Set<String> workSheets =new TreeSet<String>();
        
        try {
            wb = new HSSFWorkbook(fs);
            
            for( int ct =0; ct < wb.getNumberOfSheets(); ct++ ) {
                workSheets.add( wb.getSheetName(ct) );
            }
        
        } catch( Exception e ) {
            throw new ImportAdapterException( e );
        
        }
        
        return workSheets;
    }

    
    /**
     * 
     */
    public List<AttrDef> readHeader() throws ImportAdapterException {
        List<AttrDef> fields =new ArrayList<AttrDef>();
        
        try {
            if ( wb == null ) {
                wb = new HSSFWorkbook(fs);
            }
            
            if ( ws == null ) {
                ws =wb.getSheet( workSheetName );
            }
            
            int firstRowNum =ws.getFirstRowNum();
            HSSFRow headerRow =ws.getRow( firstRowNum );
            HSSFRow typeRow =null;
            try {
                typeRow =ws.getRow( firstRowNum + 1 );
            } catch( Exception e) {
                e.printStackTrace();
                // looks like this spreadsheet has got only one line
            }
            
            for( short cellCt =headerRow.getFirstCellNum(); cellCt < headerRow.getLastCellNum(); cellCt++ ) {
                HSSFCell cell =headerRow.getCell( cellCt ) ;
                AttrDef attrDef =(AttrDef)boFactory.get( BizObjectFactory.ATTR_DEF );
                
                // get name
                if ( firstRowContainsFieldNames ) {
                    attrDef.setName( String.valueOf( getObjectValue( cell ) ) );
                } else {
                    attrDef.setName( DEFAULT_FIELD_NAME + cellCt );
                }
                
                // get type
                if ( typeRow != null ) {
                    attrDef.setType( getType( typeRow.getCell( cellCt ) ) );
                } else {
                    attrDef.setType( DEFAULT_FIELD_TYPE );
                }
                
                fields.add( attrDef );
            }       
            
        } catch( Exception e ) {
            throw new ImportAdapterException( e );
        }
        
        return fields;
    }
   
    /**
     * 
     */
    @SuppressWarnings("unchecked")
	protected Attrs readValues( int nbRec ) throws ImportAdapterException {
        Attrs attrs =null;
       
        HSSFCell cell;
        HSSFRow row;            
        Object value;
        List<List> rows;
        
        try {
            attrs =(Attrs)boFactory.get( BizObjectFactory.ATTRS );
            
            wb = new HSSFWorkbook(fs);
            ws =wb.getSheet( workSheetName );
            
            int firstRow =ws.getFirstRowNum();
            int lastRow =ws.getLastRowNum();
            if ( ( nbRec != -1 ) && ( lastRow > nbRec  ) ) {
                lastRow =nbRec -1;
            }
            
            attrs.setHeader( readHeader() );               
                       
            rows =new ArrayList<List>();
            attrs.setRows( rows );
            
            // don't read the first line if it contains field names
            if ( firstRowContainsFieldNames ) {
                firstRow++;
            }
            
            for( int rowCt =firstRow; rowCt <= lastRow; rowCt++ ) {
                row =ws.getRow( rowCt );
                
                List<Object> rowMap =new ArrayList<Object>();
                rows.add( rowMap );
        
                short firstCell =row.getFirstCellNum();
                short lastCell =row.getLastCellNum();
               
	            for( short cellCt =firstCell; cellCt < lastCell; cellCt++ ) {
	                cell =row.getCell( cellCt );
	                value =cell != null ? getObjectValue( cell ) : null;//getNullValue( cellCt-firstCell, attrs.getHeader() );
	                
	                rowMap.add( value );
	            }
	            
	            int rowMapSize =rowMap.size();
	            
	            // Add empty value for missing cells if necessary
	            for( int missingCellCt =0; missingCellCt < attrs.getHeader().size() - rowMapSize; missingCellCt++ ) {
	            	rowMap.add( null );
	            }

            }
            
            attrs.setDsProperties( config );
        } catch( Exception e ) {
            throw new ImportAdapterException( e );
        }
        
        return attrs;
    }

    /**
     * Convert a cell value to an object value (Integer, Double or String)
     * @param in
     * @return
     */
    private Object getObjectValue( HSSFCell cell ) {
        Object value;
        
        switch( cell.getCellType() ) {
	    	case HSSFCell.CELL_TYPE_BOOLEAN:
	    	    value =new Boolean( cell.getBooleanCellValue() );
	    		break;               		
	   	    
	    	case HSSFCell.CELL_TYPE_NUMERIC:
	    	    value =new Double( cell.getNumericCellValue() );
	    		break;
	    	
	    	case HSSFCell.CELL_TYPE_ERROR:
	    	    value =new Byte( cell.getErrorCellValue() );
	    		break;
	    	
	    	case HSSFCell.CELL_TYPE_STRING:
	    	case HSSFCell.CELL_TYPE_BLANK:
	    	case HSSFCell.CELL_TYPE_FORMULA:
	    	default:
	    	    value =new String( cell.getStringCellValue() );	
	    		break;
        }
        
        return value;
    }
    
    /**
     * Convert a cell type into a java class name (Boolean, Double, String)
     * @param cell
     * @return
     */
    private String getType( HSSFCell cell ) {
        String type =String.class.getName();
        
        if ( cell != null ) {
	        switch( cell.getCellType() ) {
		    	case HSSFCell.CELL_TYPE_BOOLEAN:
		    	    type =Boolean.class.getName();
		    		break;               		
		   	    
		    	case HSSFCell.CELL_TYPE_NUMERIC:
		    	    type =Double.class.getName();
		    	    break;

	        }
        }

        
        return type;        
    }


}
